package edu.up.controle;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import edu.up.beans.FornecedorBean;
import edu.up.connection.ConnectionDB;

public class Fornecedor {
	public Fornecedor() {
		System.out.println("Construtor Fornecedor");
	}

	public ConnectionDB con = new ConnectionDB();

	public boolean verificaDados() {
		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;

		int count = 0;

		try {
			cn = con.connect();

			String sql = "select count(*) as res from fornecedores";

			// Buscar 
			st = cn.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				count = Integer.valueOf(rs.getString("res"));
			}

			System.out.println(sql);

			cn.close();

			if (count > 0)
				return true;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return false;
	}

	public int pegaUltimoId() {
		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;

		int id = 0;

		boolean res = verificaDados();

		if (res) {

			try {
				cn = con.connect();

				String sql = "select MAX(id) as id from fornecedores";

				// Buscar Alunos
				st = cn.createStatement();
				rs = st.executeQuery(sql);

				while (rs.next()) {
					if (!rs.getString("id").equals(null))
						id = Integer.valueOf(rs.getString("id"));
				}
				
				System.out.println(sql);
				cn.close();

			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} catch (ClassNotFoundException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		} else
			id = 0;

		return id;
	}

	public int gravar(FornecedorBean bean) {
		System.out.println("GRAVA FORNECEDOR!");

		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;
		int id = pegaUltimoId();
		// Antes de executar o programa execute o startup.bat em linha de
		// comando DOS
		try {
			cn = con.connect();

			id += 1;
			String sql = "insert into FORNECEDORES (id, cnpj, nome, endereco, numero, complemento, cep, cidade, estado, telefone, contato) ";
			sql += "values (" + id + ", '" + bean.getCnpj() + "', '"
					+ bean.getNome() + "', '" + bean.getEndereco() + "', '"
					+ bean.getNumero() + "', '" + bean.getComplemento()
					+ "', '" + bean.getCep() + "', '" + bean.getCidade()
					+ "', '" + bean.getEstado() + "', '" + bean.getTelefone()
					+ "', '" + bean.getContato() + "');";

			// Buscar
			st = cn.createStatement();
			rs = st.executeQuery(sql);

			System.out.println(sql);
			cn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 1;
	}

	public int pegaId(FornecedorBean bean) {
		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;

		int id = 0;

		try {
			cn = con.connect();

			String sql = "select id as id from fornecedores where cnpj = '"
					+ bean.getCnpj() + "'"; // and senha='"+bean.getSenha()+"'";

			// Buscar ID's
			st = cn.createStatement();
			rs = st.executeQuery(sql);

			while (rs.next()) {
				id = Integer.valueOf(rs.getString("id"));
			}

			System.out.println(sql);
			cn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return id;
	}
	
	public ArrayList<String> getFornecedores() {
		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;
		ArrayList<String> bean = new ArrayList<String>();
		int id = 0;

		try {
			cn = con.connect();

			String sql = "select nome as nome from fornecedores";

			// Buscar nomes
			st = cn.createStatement();
			rs = st.executeQuery(sql);
			
			while (rs.next()) {
				String nome = rs.getString("nome");
				bean.add(nome);
			}
			
			System.out.println(sql);
			cn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return bean;
	}

	public int alterar(FornecedorBean bean) {
		System.out.println("ALTERAR FORNECEDOR!");

		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;

		// Antes de executar o programa execute o startup.bat em linha de
		// comando DOS
		try {
			cn = con.connect();
			
			int id;
			if (bean.getId() == 0) {
				id = pegaId(bean);
			} else {
				id = bean.getId();
			}
			String sql = "update fornecedores set " 
					//+ "nome = '"+ bean.getNome() + "'," 
					+ "endereco = '"+ bean.getEndereco() + "'" 
					+ ",numero = '"+ bean.getNumero() + "'" 
					+ ",complemento = '"+ bean.getComplemento() + "'" 
					+ ",cep = '" + bean.getCep()+ "'" 
					+ ",cidade = '" + bean.getCidade() + "'"
					+ ",estado = '" + bean.getEstado() + "'" 
					+ ",telefone = '"+ bean.getTelefone() + "'" 
					+ ",contato = '"+ bean.getContato() + "'" 
					+ " where id = " + id;

			st = cn.createStatement();
			rs = st.executeQuery(sql);

			System.out.println(sql);
			cn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 1;
	}

	public int excluir(FornecedorBean bean) {
		System.out.println("EXCLUIR FORNECEDOR!");

		Connection cn = null;
		Statement st = null;
		ResultSet rs = null;

		try {
			cn = con.connect();
			int id = pegaId(bean);
			String sql = "delete from FORNECEDORES where id =" + id;

			st = cn.createStatement();
			rs = st.executeQuery(sql);

			System.out.println(sql);
			cn.close();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return 1 ;
	}
}
